---
title: MySQL 指南
description: MySQL 一些常用命令、权限配置等
---

# MySQL 管理操作

本文档提供 MySQL（或 MariaDB）数据库管理中除增删改查（CRUD）外的常见操作指南，适合初学者和管理员快速查阅。内容包括用户管理、权限分配、刷新权限以及其他实用管理任务，并附有学习资源推荐。适用于 Ubuntu Server 24.04 LTS 等环境中的 MySQL/MariaDB。

## 1. 用户管理

### 1.1 创建用户
在 MySQL 中，可以通过 `CREATE USER` 命令创建新用户。

**语法**：
```sql
CREATE USER 'username'@'host' IDENTIFIED WITH auth_plugin BY 'password';
```

- `username`：用户名。
- `host`：允许用户连接的主机（`localhost` 表示本地，`%` 表示任意主机）。
- `auth_plugin`：认证插件，常用 `mysql_native_password`（传统密码认证）或 `auth_socket`（本地用户认证）。
- `password`：用户密码（若使用 `auth_socket`，可省略）。

**示例**：
- 创建本地用户（使用密码认证）：
```sql
CREATE USER 'new_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'SecurePass123!';
  ```
- 创建允许远程连接的用户：
```sql
CREATE USER 'remote_user'@'%' IDENTIFIED WITH mysql_native_password BY 'RemotePass123!';
  ```

**注意**：
- 密码应包含大小写字母、数字和特殊字符，长度至少 8 位（若启用了 `VALIDATE PASSWORD` 插件）。
- 使用 `auth_socket`（Ubuntu 默认 root 用户认证方式）无需密码，但仅限本地登录。

### 1.2 修改用户
通过 `ALTER USER` 命令修改用户认证方式或密码。

**语法**：
```sql
ALTER USER 'username'@'host' IDENTIFIED WITH auth_plugin BY 'new_password';
```

**示例**：
- 修改 root 用户为密码认证：
```sql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'MyRootPass123!';
  ```
- 重置用户密码：
```sql
ALTER USER 'new_user'@'localhost' IDENTIFIED BY 'NewPass456!';
  ```

### 1.3 删除用户
通过 `DROP USER` 命令删除用户。

**语法**：
```sql
DROP USER 'username'@'host';
```

**示例**：
```sql
DROP USER 'new_user'@'localhost';
```

**注意**：删除用户前，建议检查用户是否存在：
```sql
SELECT User, Host FROM mysql.user;
```

## 2. 权限管理

### 2.1 授予权限
通过 `GRANT` 命令为用户分配权限，支持数据库、表或特定操作的权限。

**语法**：
```sql
GRANT privilege_list ON database.table TO 'username'@'host';
```

- `privilege_list`：权限列表（如 `SELECT`, `INSERT`, `UPDATE`, `DELETE`, `ALL PRIVILEGES`）。
- `database.table`：目标数据库和表（`*.*` 表示所有数据库和表）。
- `username@host`：目标用户。

**示例**：
- 授予用户对特定表（如 `mydb.mytable`）的读写权限：
```sql
GRANT SELECT, INSERT, UPDATE ON mydb.mytable TO 'new_user'@'localhost';
  ```
- 授予用户对整个数据库的全部权限：
```sql
GRANT ALL PRIVILEGES ON mydb.* TO 'new_user'@'localhost';
  ```
- 授予全局权限（谨慎使用）：
```sql
GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%';
  ```

**常见权限**：
- `SELECT`：读取数据。
- `INSERT`：插入数据。
- `UPDATE`：更新数据。
- `DELETE`：删除数据。
- `CREATE`, `DROP`：创建/删除表或数据库。
- `GRANT OPTION`：允许用户将权限授予他人。

### 2.2 查看权限
检查用户权限：
```sql
SHOW GRANTS FOR 'username'@'host';
```

**示例**：
```sql
SHOW GRANTS FOR 'new_user'@'localhost';
```

### 2.3 撤销权限
通过 `REVOKE` 命令撤销权限。

**语法**：
```sql
REVOKE privilege_list ON database.table FROM 'username'@'host';
```

**示例**：
- 撤销特定表的权限：
```sql
REVOKE INSERT, UPDATE ON mydb.mytable FROM 'new_user'@'localhost';
  ```
- 撤销所有权限：
```sql
REVOKE ALL PRIVILEGES ON mydb.* FROM 'new_user'@'localhost';
  ```

## 3. 刷新权限
权限更改后，需通过 `FLUSH PRIVILEGES` 使更改立即生效。

**语法**：
```sql
FLUSH PRIVILEGES;
```

**示例**：
```sql
GRANT SELECT ON mydb.mytable TO 'new_user'@'localhost';
FLUSH PRIVILEGES;
```

**注意**：在大多数情况下，`GRANT` 和 `REVOKE` 命令会自动刷新权限，但手动运行 `FLUSH PRIVILEGES` 是安全的做法。

## 4. 其他管理操作

### 4.1 安全配置（`mysql_secure_installation`）
运行以下命令加固 MySQL 安装：
```bash
sudo mysql_secure_installation
```

**常见步骤**：
- 设置密码验证插件（VALIDATE PASSWORD）。
- 删除匿名用户（建议 `y`）。
- 禁用 root 远程登录（建议 `y`）。
- 删除测试数据库（建议 `y`）。
- 刷新权限表（建议 `y`）。

### 4.2 查看用户列表
列出所有 MySQL 用户：
```sql
SELECT User, Host FROM mysql.user;
```

### 4.3 更改默认认证插件
如果需要更改用户认证方式（例如从 `auth_socket` 到 `mysql_native_password`）：
```sql
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YourPassword123!';
FLUSH PRIVILEGES;
```

### 4.4 检查 MySQL 服务状态
在终端运行：
```bash
sudo systemctl status mysql
```

重启服务：
```bash
sudo systemctl restart mysql
```

### 4.5 配置网络访问
默认情况下，MySQL 可能仅监听本地地址（`127.0.0.1`）。若需允许远程连接：
1. 编辑 MySQL 配置文件（例如 `/etc/mysql/my.cnf` 或 `/etc/mysql/mariadb.conf.d/50-server.cnf`）：
```ini
[mysqld]
bind-address = 0.0.0.0
   ```
2. 重启 MySQL：
```bash
sudo systemctl restart mysql
   ```
3. 配置防火墙（例如 `ufw`）：
```bash
sudo ufw allow from <remote_ip> to any port 3306
   ```

**注意**：远程访问需创建专用用户并设置强密码。

### 4.6 查看错误日志
检查 MySQL 错误日志以排查问题：
```bash
sudo tail -f /var/log/mysql/error.log
```

## 5. 常用命令

### 5.1 数据库操作

- **显示所有数据库**
  ```sql
  SHOW DATABASES;
  ```

- **创建新数据库**
  ```sql
  CREATE DATABASE database_name;
  ```

- **使用数据库**
  ```sql
  USE database_name;
  ```

- **删除数据库**
  ```sql
  DROP DATABASE database_name;
  ```

### 5.2 数据表操作

- **显示当前数据库的所有表**
  ```sql
  SHOW TABLES;
  ```

- **创建新表**
  ```sql
  CREATE TABLE table_name (
      column1 datatype,
      column2 datatype,
      ...
  );
  ```

- **查看表结构**
  ```sql
  DESCRIBE table_name;
  ```

- **删除表**
  ```sql
  DROP TABLE table_name;
  ```

## 6. 备份与恢复

数据备份是数据库管理的生命线。主要分为逻辑备份和物理备份。

### 6.1 逻辑备份 (`mysqldump`)

`mysqldump` 是最常用的逻辑备份工具，它将数据库结构和数据导出为 SQL 文件。

**常用命令**：

- **备份所有数据库**：
  ```bash
  mysqldump -u root -p --all-databases > all_databases_backup.sql
  ```

- **备份指定的一个或多个数据库**：
  ```bash
  mysqldump -u root -p --databases mydb1 mydb2 > specific_databases_backup.sql
  ```

- **备份特定表**：
  ```bash
  mysqldump -u root -p mydb mytable1 mytable2 > specific_tables_backup.sql
  ```

- **仅备份数据库结构（不含数据）**：
  ```bash
  mysqldump -u root -p --no-data mydb > schema_only_backup.sql
  ```

<Callout title="最佳实践：InnoDB 备份">
对于使用 InnoDB 存储引擎的数据库，添加 `--single-transaction` 选项可以在不锁定表的情况下创建一致性快照，对线上服务影响最小。
```bash
mysqldump -u root -p --single-transaction --all-databases > consistent_backup.sql
```
</Callout>

### 6.2 数据恢复

从 `mysqldump` 创建的备份文件中恢复数据。

```bash
# 登录到 MySQL
mysql -u root -p

# 如果需要，创建数据库
CREATE DATABASE mydb;

# 退出 MySQL，然后导入数据
exit

# 从备份文件导入
mysql -u root -p mydb < specific_databases_backup.sql
```

### 6.3 物理备份与时间点恢复 (PITR)

- **物理备份**：直接复制数据库文件，恢复速度快。常用工具如 **Percona XtraBackup**，支持热备份。
- **二进制日志 (Binary Log)**：记录所有数据更改操作。结合全量备份和二进制日志，可以实现时间点恢复（Point-in-Time Recovery），将数据库恢复到任意时刻。

---

## 7. 性能监控与优化

### 7.1 查看当前进程

`SHOW PROCESSLIST` 可以查看当前所有连接的线程状态，用于排查慢查询或连接数问题。

```sql
SHOW FULL PROCESSLIST;
```

### 7.2 分析查询性能 (`EXPLAIN`)

`EXPLAIN` 是优化 SQL 查询的利器。将它放在 `SELECT` 语句前，可以分析查询的执行计划，判断是否使用了索引。

```sql
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
```
关注 `type`（最好是 `ref`, `eq_ref`, `const`）和 `key`（实际使用的索引）列。

### 7.3 慢查询日志

开启慢查询日志可以记录执行时间超过指定阈值的查询，是定位性能瓶颈的重要手段。在配置文件 (`my.cnf`) 中设置：

```ini
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2  # 记录超过 2 秒的查询
```

### 7.4 关键配置参数

- `innodb_buffer_pool_size`：InnoDB 缓冲池大小，最重要的性能参数之一，通常建议设为物理内存的 50%-70%。
- `max_connections`：最大连接数，根据应用需求调整。

---

## 8. 最佳实践与安全建议

- **最小权限原则**：为应用用户授予完成其任务所需的最小权限集，避免使用 `ALL PRIVILEGES`。
- **定期审计**：定期使用 `SHOW GRANTS` 检查用户权限，移除不再需要的用户和权限。
- **使用强密码**：结合 `VALIDATE PASSWORD` 插件强制使用复杂密码。
- **限制网络访问**：默认只允许本地连接。如需远程访问，使用防火墙精确控制来源 IP，并为远程用户创建专用账户。
- **禁用 `root` 远程登录**：这是 `mysql_secure_installation` 强调的关键安全步骤。
- **定期备份**：制定并严格执行备份策略，并定期测试恢复流程的有效性。
- **版本更新**：及时更新 MySQL/MariaDB 到最新的稳定版本，以获取安全补丁和性能改进。
- **在测试环境验证**：所有重要的 schema 变更、权限修改或配置调整，都应先在测试环境中充分验证。
- **MariaDB 兼容性**：Ubuntu 24.04 可能默认安装 MariaDB，命令和配置类似，但注意配置文件路径差异。